package com.trw.trp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.trw.trp.AppConfiguration;
import com.trw.trp.bean.RoleBean;
import com.trw.trp.bean.UserBean;
import com.trw.trp.bean.UserSearchCriteriaBean;
import com.trw.trp.msg.ResultMessage;
import com.utuan.log.Log;
import com.utuan.log.LogFactory;

public class UserDao extends AbstractDao {

	private static Log log = LogFactory.getLog(UserDao.class);

	public enum CHANGE_PWD_RESULT {
		OLD_PWD_WRONG, SUCCESS, FAILED
	}

	private static final String SQL_LOGIN = "select userid from users where email=? and pwd=?";
	private static final String SQL_VERIFY_USER = "select userid from users where email = ?";

	private static final String SQL_GET_USER = "SELECT u.userid,u.email,u.name,u.division,u.department,u.position,u.tel,u.mobile FROM trp.users u where u.userid=?";
	private static final String SQL_GET_USER_ROLE = "select r.role_id,r.role_name from trp.users u,trp.user_roles ur,trp.roles r where u.userid=? and u.userid=ur.user_id and ur.role_id=r.role_id";
	private static final String SQL_GET_USERS = "SELECT u.userid,u.email,u.name,u.division,u.department,u.position,u.tel,u.mobile FROM trp.users u where 1=1";
	private static final String SQL_GET_USER_PWD = "select u.pwd from trp.users u where u.email=?";
	private static final String SQL_DELETE_USER = "DELETE FROM TRP.USERS WHERE USERID=?";

	private static final String SQL_REGISTER = "insert into users(email,pwd,name,division,department,position,tel,mobile) values(?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_USER = "update trp.users set email=?,name=?,division=?,department=?,position=?,tel=?,mobile=? where userid=?";
	private static final String SQL_REMOVE_USER_ROLES = "delete from trp.user_roles where user_id=?";
	private static final String SQL_INSERT_USER_ROLE = "insert into trp.user_roles(user_id,role_id) values(?,?)";

	private static final String SQL_GET_ROLES = "select r.role_id,r.role_name from trp.roles r";

	private static final String SQL_CHECK_USER_CURRENT_PWD = "SELECT 1 FROM TRP.USERS WHERE PWD=? AND USERID=?";
	private static final String SQL_CHANGE_USER_PASSWORD = "UPDATE TRP.USERS SET PWD=? WHERE USERID=?";

	UserDao() {
	}

	public long login(String userId, String password) {
		long ret = -1;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_LOGIN);
			ps.setString(1, userId);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if (rs.next()) {
				ret = rs.getLong(1);
			}
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			close(conn, rs, ps);
		}
		return ret;
	}

	public ResultMessage register(UserBean user, String password) {
		ResultMessage ret = new ResultMessage();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_REGISTER);
			int i = 1;
			ps.setString(i++, user.getEmail());
			ps.setString(i++, password);
			ps.setString(i++, user.getName());
			ps.setString(i++, user.getDivision());
			ps.setString(i++, user.getDepartment());
			ps.setString(i++, user.getPosition());
			ps.setString(i++, user.getTel());
			ps.setString(i++, user.getMobile());
			ps.execute();
			ret.setState(ResultMessage.SUCCESS);
			ret.setMessage(AppConfiguration.getInstance().getProperty(
					"user.register.ok"));
			conn.commit();
		} catch (SQLException e) {
			ret.setState(ResultMessage.FAILED);
			ret.setMessage(AppConfiguration.getInstance()
					.getProperty("db.fail"));
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			close(conn, rs, ps);
		}
		return ret;
	}

	public ResultMessage verify(String userId) {
		ResultMessage ret = new ResultMessage();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_VERIFY_USER);
			ps.setString(1, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				ret.setState(ResultMessage.FAILED);
				ret.setMessage(AppConfiguration.getInstance().getProperty(
						"user.verify.fail"));
			} else {
				ret.setState(ResultMessage.SUCCESS);
				ret.setMessage(AppConfiguration.getInstance().getProperty(
						"user.verify.ok"));
			}
		} catch (SQLException e) {
			ret.setState(ResultMessage.FAILED);
			ret.setMessage(AppConfiguration.getInstance()
					.getProperty("db.fail"));
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			close(conn, rs, ps);
		}
		return ret;
	}

	public String getUserPwd(String email) {
		String pwd = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_USER_PWD);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if (rs.next()) {
				pwd = rs.getString(1);
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(null, rs);
			close(conn, rs, ps);
		}
		return pwd;
	}

	public UserBean getUser(long userId) {
		UserBean user = null;
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_USER);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			List<RoleBean> roleList = new ArrayList<RoleBean>();
			if (rs.next()) {
				user = new UserBean();
				int i = 1;
				user.setUserId(rs.getLong(i++));
				user.setEmail(rs.getString(i++));
				user.setName(rs.getString(i++));
				user.setDivision(rs.getString(i++));
				user.setDepartment(rs.getString(i++));
				user.setPosition(rs.getString(i++));
				user.setTel(rs.getString(i++));
				user.setMobile(rs.getString(i++));
				user.setRoleList(roleList);
			}
			if (user != null) {
				ps2 = conn.prepareStatement(SQL_GET_USER_ROLE);
				ps2.setLong(1, userId);
				rs2 = ps2.executeQuery();
				while (rs2.next()) {
					RoleBean role = new RoleBean(rs2.getLong(1));
					role.setRoleName(rs.getString(2));
					roleList.add(role);
				}
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(null, rs2);
			close(conn, rs, ps);
		}
		return user;
	}

	public List<UserBean> getUsers(UserSearchCriteriaBean criteria) {
		List<UserBean> userList = new ArrayList<UserBean>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			StringBuilder sb = new StringBuilder(SQL_GET_USERS);
			if (criteria != null && criteria.getPosition() != null) {
				sb.append(" and u.position=?");
			}
			if (criteria != null && criteria.getDepartment() != null) {
				sb.append(" and u.department=?");
			}
			ps = conn.prepareStatement(sb.toString());
			int i = 1;
			if (criteria != null && criteria.getPosition() != null) {
				ps.setString(i++, criteria.getPosition());
			}
			if (criteria != null && criteria.getDepartment() != null) {
				ps.setString(i++, criteria.getDepartment());
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				UserBean user = new UserBean();
				i = 1;
				user.setUserId(rs.getLong(i++));
				user.setEmail(rs.getString(i++));
				user.setName(rs.getString(i++));
				user.setDivision(rs.getString(i++));
				user.setDepartment(rs.getString(i++));
				user.setPosition(rs.getString(i++));
				user.setTel(rs.getString(i++));
				user.setMobile(rs.getString(i++));
				userList.add(user);
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}
		return userList;
	}

	public boolean updateUser(UserBean user) {
		boolean success = true;
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_UPDATE_USER);
			int i = 1;
			ps.setString(i++, user.getEmail());
			ps.setString(i++, user.getName());
			ps.setString(i++, user.getDivision());
			ps.setString(i++, user.getDepartment());
			ps.setString(i++, user.getPosition());
			ps.setString(i++, user.getTel());
			ps.setString(i++, user.getMobile());
			ps.setLong(i++, user.getUserId());
			ps.execute();

			ps2 = conn.prepareStatement(SQL_REMOVE_USER_ROLES);
			ps2.setLong(1, user.getUserId());
			ps2.execute();

			ps3 = conn.prepareStatement(SQL_INSERT_USER_ROLE);
			List<RoleBean> roleList = user.getRoleList();
			if (roleList != null && !roleList.isEmpty()) {
				for (RoleBean role : roleList) {
					ps3.setLong(1, user.getUserId());
					ps3.setLong(2, role.getRoleId());
					ps3.execute();
				}
			}
			conn.commit();
		} catch (SQLException e) {
			success = false;
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps, ps2, ps3);
		}
		return success;
	}

	public List<RoleBean> getRoles() {
		List<RoleBean> roleList = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			ps = conn.prepareStatement(SQL_GET_ROLES);
			rs = ps.executeQuery();
			while (rs.next()) {
				RoleBean role = new RoleBean(rs.getLong(1));
				role.setRoleName(rs.getString(2));
				if (roleList == null) {
					roleList = new ArrayList<RoleBean>();
				}
				roleList.add(role);
			}
		} catch (SQLException e) {
			log.warn(e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps);
		}
		return roleList;
	}

	public boolean deleteUser(long userId) {
		boolean ret = false;
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		try {
			conn = getConnection();
			if (conn == null) {
				log.debug("can't get db connection.");
				return ret;
			}
			ps = conn.prepareStatement(SQL_REMOVE_USER_ROLES);
			ps.setLong(1, userId);
			ps.execute();
			ps2 = conn.prepareStatement(SQL_DELETE_USER);
			ps2.setLong(1, userId);
			ps2.execute();
			conn.commit();
			ret = true;
		} catch (SQLException e) {
			log.warn(e);
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, null, ps, ps2);
		}
		return ret;
	}

	public CHANGE_PWD_RESULT changePassword(long userId, String oldPwd,
			String newPwd) {
		CHANGE_PWD_RESULT ret = null;
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			if (conn == null) {
				ret = CHANGE_PWD_RESULT.FAILED;
				log.debug("can't get db connection.");
				return ret;
			}
			ps = conn.prepareStatement(SQL_CHECK_USER_CURRENT_PWD);
			ps.setString(1, oldPwd);
			ps.setLong(2, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				ps2 = conn.prepareStatement(SQL_CHANGE_USER_PASSWORD);
				ps2.setString(1, newPwd);
				ps2.setLong(2, userId);
				ps2.execute();
				conn.commit();
				ret = CHANGE_PWD_RESULT.SUCCESS;
			} else {
				ret = CHANGE_PWD_RESULT.OLD_PWD_WRONG;
			}
		} catch (SQLException e) {
			ret = CHANGE_PWD_RESULT.FAILED;
			log.warn(e);
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException e1) {
				log.warn(e1);
			}
		} finally {
			close(conn, rs, ps, ps2);
		}
		return ret;
	}

}
